/*
������� ������������� � ����

create role taskadmin login createdb createrole password '123qwe';
alter user taskadmin superuser;

# vi /var/db/pgsql/pg_hba.conf
host    all         all         127.0.0.1/32          trust

# psql -U taskadmin template1
> create database tasks encoding 'utf-8';
> \c tasks
*/

-- CREATE DATABASE tasks ENCODING='UTF8';
-- CREATE USER taskwww WITH PASSWORD '123qwe';

CREATE SEQUENCE gen_diary_id INCREMENT 1 START WITH 1 CYCLE;

CREATE TABLE diary
(
    diary_id            BIGINT NOT NULL UNIQUE DEFAULT nextval('gen_diary_id'::text),
    user_id             INTEGER NOT NULL,
    parent_task         INTEGER,
    rec_date            DATE NOT NULL,
    prio                SMALLINT DEFAULT '1',
    txt                 TEXT NOT NULL,
    comp_date           TIMESTAMP,
    comp_type           SMALLINT,
    comp_txt            TEXT,
    PRIMARY KEY(diary_id),
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

GRANT SELECT,INSERT,UPDATE,DELETE ON diary TO taskwww;
GRANT SELECT,UPDATE ON gen_diary_id TO taskwww;

-- 2009-04-06

CREATE SEQUENCE gen_notes_id INCREMENT 1 START WITH 1 CYCLE;

CREATE TABLE diary_notes
(
    note_id             BIGINT NOT NULL UNIQUE DEFAULT nextval('gen_notes_id'::text),
    user_id             INTEGER NOT NULL,
    rec_date            DATE NOT NULL DEFAULT current_date,
    txt                 TEXT NOT NULL,
    PRIMARY KEY(note_id),
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE UNIQUE INDEX idx_uniq_date ON diary_notes (user_id,rec_date);

GRANT SELECT,INSERT,UPDATE,DELETE ON diary_notes TO taskwww;
GRANT SELECT,UPDATE ON gen_notes_id TO taskwww;

-- 2009-08-14

CREATE SEQUENCE gen_tag_id INCREMENT 1 START WITH 1 CYCLE;

CREATE TABLE tags
(
    tag_id      BIGINT NOT NULL UNIQUE DEFAULT nextval('gen_tag_id'::text),
    user_id     INTEGER NOT NULL,
    tag         VARCHAR(128) NOT NULL,
    PRIMARY KEY(tag_id),
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

GRANT SELECT,INSERT,UPDATE,DELETE ON tags TO taskwww;
GRANT SELECT,UPDATE ON gen_tag_id TO taskwww;

CREATE TABLE tags_dist
(
    tag_id      BIGINT NOT NULL,
    diary_id    BIGINT NOT NULL,
    PRIMARY KEY(tag_id, diary_id),
    FOREIGN KEY(tag_id) REFERENCES tags(tag_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(diary_id) REFERENCES diary(diary_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE UNIQUE INDEX idx_uniq_tags ON tags_dist (tag_id,diary_id);

GRANT SELECT,INSERT,UPDATE,DELETE ON tags_dist TO taskwww;

-- 2009-08-28

CREATE FUNCTION _group_concat(text, text, text)
RETURNS text AS $$
    SELECT CASE
        WHEN $2 IS NULL THEN $1
        WHEN $1 IS NULL THEN $2
        ELSE $1 operator(pg_catalog.||) $3 operator(pg_catalog.||) $2
    END
$$ IMMUTABLE LANGUAGE SQL;

CREATE AGGREGATE group_concat(text, text) (
    SFUNC = _group_concat,
    STYPE = text
);

-- 2009-11-09 Move texts to special table

CREATE SEQUENCE gen_txt_id INCREMENT 1 START WITH 1 CYCLE;

CREATE TABLE texts
(
    txt_id      BIGINT NOT NULL UNIQUE DEFAULT nextval('gen_txt_id'::text),
    user_id     INTEGER NOT NULL,
    txt         TEXT NOT NULL,
    PRIMARY KEY(txt_id),
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

GRANT SELECT,INSERT,UPDATE,DELETE ON texts TO taskwww;
GRANT SELECT,UPDATE ON gen_txt_id TO taskwww;

ALTER TABLE texts ADD fti_txt tsvector;
UPDATE texts SET fti_txt=to_tsvector(txt);
VACUUM FULL ANALYZE;
CREATE INDEX fti_texts_idx ON texts USING gist(fti_txt);
VACUUM FULL ANALYZE;
CREATE TRIGGER tg_fti_texts BEFORE UPDATE OR INSERT ON texts FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(fti_txt, 'pg_catalog.russian', txt);

ALTER TABLE diary ADD txt_id BIGINT;

-- Fill texts with data from diary!
-- Execute in server console command "createlang -U taskadmin plpgsql tasks" to enable plpgsql on DB
CREATE OR REPLACE FUNCTION conv_txt_diary() RETURNS INTEGER
AS $$
    DECLARE i INTEGER;
    DECLARE curr_txt_id BIGINT;
    DECLARE rw RECORD;
BEGIN
    i := 0;
    FOR rw IN SELECT diary_id, user_id, txt FROM diary LOOP
        INSERT INTO texts(user_id, txt) VALUES(rw.user_id, rw.txt);
        UPDATE diary SET txt_id=currval('gen_txt_id') WHERE diary_id=rw.diary_id;
        i := i + 1;
    END LOOP;
    
    RETURN i;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM conv_txt_diary();

ALTER TABLE diary ALTER txt_id SET NOT NULL;
ALTER TABLE diary ADD CONSTRAINT textsfk FOREIGN KEY(txt_id) REFERENCES texts(txt_id) ON UPDATE CASCADE ON DELETE RESTRICT;

-- Move comp_txt to texts

ALTER TABLE diary ADD comp_txt_id BIGINT;
ALTER TABLE diary ALTER comp_txt_id SET DEFAULT NULL;

CREATE OR REPLACE FUNCTION conv_comp_txt_diary() RETURNS INTEGER
AS $$
    DECLARE i INTEGER;
    DECLARE curr_txt_id BIGINT;
    DECLARE rw RECORD;
BEGIN
    i := 0;
    FOR rw IN SELECT diary_id, user_id, comp_txt FROM diary WHERE comp_txt IS NOT NULL LOOP
        INSERT INTO texts(user_id, txt) VALUES(rw.user_id, rw.comp_txt);
        UPDATE diary SET comp_txt_id=currval('gen_txt_id') WHERE diary_id=rw.diary_id;
        i := i + 1;
    END LOOP;
    
    RETURN i;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM conv_comp_txt_diary();

-- Deleting old texts from diary

DROP TRIGGER tg_fti_txt ON diary; 
DROP INDEX fti_txt_idx;
ALTER TABLE diary DROP fti_txt;
ALTER TABLE diary DROP txt, DROP comp_txt;

-- Move notes to texts

ALTER TABLE diary_notes ADD txt_id BIGINT;

CREATE OR REPLACE FUNCTION conv_txt_notes() RETURNS INTEGER
AS $$
    DECLARE i INTEGER;
    DECLARE curr_txt_id BIGINT;
    DECLARE rw RECORD;
BEGIN
    i := 0;
    FOR rw IN SELECT note_id, user_id, txt FROM diary_notes LOOP
        INSERT INTO texts(user_id, txt) VALUES(rw.user_id, rw.txt);
        UPDATE diary_notes SET txt_id=currval('gen_txt_id') WHERE note_id=rw.note_id;
        i := i + 1;
    END LOOP;
    
    RETURN i;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM conv_txt_notes();

ALTER TABLE diary_notes ALTER txt_id SET NOT NULL;
ALTER TABLE diary_notes ADD CONSTRAINT notes_txt_fk FOREIGN KEY(txt_id) REFERENCES texts(txt_id) ON UPDATE CASCADE ON DELETE RESTRICT;

-- Delete old notes txt

DROP TRIGGER tg_fti_notes_txt ON diary_notes;
DROP INDEX fti_notes_txt_idx;
ALTER TABLE diary_notes DROP fti_txt;
ALTER TABLE diary_notes DROP txt;

-- 2010-01-15

CREATE SEQUENCE gen_contact_id INCREMENT 1 START WITH 1 CYCLE;

CREATE TABLE contacts
(
    contact_id      BIGINT NOT NULL UNIQUE DEFAULT nextval('gen_contact_id'::text),
    user_id         INTEGER NOT NULL,
    name            VARCHAR(255) NOT NULL,
    PRIMARY KEY(contact_id),
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

GRANT SELECT,INSERT,UPDATE,DELETE ON contacts TO taskwww;
GRANT SELECT,UPDATE ON gen_contact_id TO taskwww;

CREATE SEQUENCE gen_contact_group_id INCREMENT 1 START WITH 1 CYCLE;

CREATE TABLE contact_group
(
    group_id    BIGINT NOT NULL UNIQUE DEFAULT nextval('gen_contact_group_id'::text),
    name        VARCHAR(255) NOT NULL,
    PRIMARY KEY(group_id)
);

GRANT SELECT,INSERT,UPDATE,DELETE ON contact_group TO taskwww;
GRANT SELECT,UPDATE ON gen_contact_group_id TO taskwww;

CREATE TABLE cont_group_dist
(
    contact_id  BIGINT NOT NULL,
    group_id    BIGINT NOT NULL,
    PRIMARY KEY(contact_id, group_id),
    FOREIGN KEY(contact_id) REFERENCES contacts(contact_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(group_id) REFERENCES contact_group(group_id) ON DELETE CASCADE ON UPDATE CASCADE
);

GRANT SELECT,INSERT,UPDATE,DELETE ON cont_group_dist TO taskwww;

CREATE SEQUENCE gen_cont_data_type_id INCREMENT 1 START WITH 1 CYCLE;

CREATE TABLE contact_data_types
(
    data_type_id    BIGINT NOT NULL UNIQUE DEFAULT nextval('gen_cont_data_type_id'::text),
    name            VARCHAR(255) NOT NULL,
    PRIMARY KEY(data_type_id)
);

GRANT SELECT ON contact_data_types TO taskwww;

CREATE SEQUENCE gen_cont_data_id INCREMENT 1 START WITH 1 CYCLE; 

CREATE TABLE contact_data
(
    data_id         BIGINT NOT NULL UNIQUE DEFAULT nextval('gen_cont_data_id'::text),
    contact_id      BIGINT NOT NULL,
    data_type_id    BIGINT NOT NULL,
    txt_data        VARCHAR(255) NOT NULL,
    PRIMARY KEY(data_id),
    FOREIGN KEY(contact_id) REFERENCES contacts(contact_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(data_type_id) REFERENCES contact_data_types(data_type_id) ON DELETE CASCADE ON UPDATE CASCADE
);

GRANT SELECT,INSERT,UPDATE,DELETE ON contact_data TO taskwww;
GRANT SELECT,UPDATE ON gen_cont_data_id TO taskwww;

ALTER TABLE contacts ADD txt_id BIGINT DEFAULT NULL;
ALTER TABLE contacts ADD FOREIGN KEY(txt_id) REFERENCES texts(txt_id) ON DELETE SET NULL ON UPDATE CASCADE;

-- 2010-01-18 Добавляем индексы к таблицам с FOREIGN KEY для ускорения соединений и удаления

CREATE INDEX diary_user ON diary(user_id);
CREATE INDEX notes_user ON diary_notes(user_id);
CREATE INDEX tags_user ON tags(user_id);
CREATE INDEX cont_user ON contacts(user_id);
CREATE INDEX cont_data001 ON contact_data(contact_id);
CREATE INDEX cont_data002 ON contact_data(data_type_id);

-- 2010-02-04 Добавляю поле пользователя в таблицу групп контактов

ALTER TABLE contact_group ADD user_id INTEGER;
ALTER TABLE contact_group ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE contact_group ADD FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX cont_group_user ON contact_group(group_id);

-- 2010-02-27 Сокращенное обозначение типов данных контакта

ALTER TABLE contact_data_types ADD short VARCHAR(10);

-- 2010-02-27 Добавление поля со временем последнего изменения в таблицу с текстами

ALTER TABLE texts ADD last_update timestamp default now();

CREATE FUNCTION txt_last_update() RETURNS trigger AS '
BEGIN
    new.last_update := CURRENT_TIMESTAMP;
    RETURN new;
END
' LANGUAGE 'plpgsql';

CREATE TRIGGER tg_txt_last_update BEFORE UPDATE ON texts FOR EACH ROW EXECUTE PROCEDURE txt_last_update();

-- 2010-03-12 Ремонт прав для backup

-- С этого момента важно помнить не забывать назначать нужные права на новые таблицы и прочие
-- объекты, а также проверять после этого работу скрипта backup.

GRANT SELECT ON SEQUENCE gen_cont_data_id,gen_cont_data_type_id,gen_contact_group_id,gen_contact_id,gen_tag_id,gen_txt_id TO diary_backup;
GRANT SELECT ON TABLE cont_group_dist,contact_data,contact_data_types,contact_group,contacts,tags,tags_dist,texts TO diary_backup;

-- 2010-03-12 Добавляем таблицы для задач

ALTER TABLE diary ADD task_id BIGINT NOT NULL DEFAULT '0';

CREATE SEQUENCE gen_task_id INCREMENT 1 START WITH 1 CYCLE;

CREATE TABLE tasks
(
    task_id     BIGINT NOT NULL UNIQUE DEFAULT nextval('gen_task_id'::text),
    user_id     INTEGER NOT NULL,
    title       VARCHAR(255) NOT NULL,
    txt_id      BIGINT NOT NULL,
    comp_type   SMALLINT DEFAULT NULL,
    comp_txt_id BIGINT DEFAULT NULL,
    start_date  DATE NOT NULL,
    end_date    DATE NOT NULL,
    comp_date   DATE DEFAULT NULL,
    prio        SMALLINT DEFAULT '1',
    PRIMARY KEY (task_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (txt_id) REFERENCES texts(txt_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE INDEX tasks_users ON tasks(user_id);
CREATE INDEX tasks_txt ON tasks(txt_id);
CREATE INDEX tasks_comp_txt ON tasks(comp_txt_id);

GRANT SELECT,INSERT,UPDATE,DELETE ON tasks TO taskwww;
GRANT SELECT,UPDATE ON gen_task_id TO taskwww;

CREATE TABLE tasks_tags_dist
(
    tag_id      BIGINT NOT NULL,
    task_id     BIGINT NOT NULL,
    PRIMARY KEY (tag_id, task_id),
    FOREIGN KEY (tag_id) REFERENCES tags(tag_id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON UPDATE CASCADE ON DELETE CASCADE
);

GRANT SELECT ON SEQUENCE gen_task_id TO diary_backup;
GRANT SELECT ON TABLE tasks, tasks_tags_dist TO diary_backup;

-- 2011-03-05 Добавить DEFAULT '0' на тип завершения задачи.
ALTER TABLE diary ALTER comp_type SET DEFAULT '0';